﻿Option Strict On

Imports DbDataLib
Imports KUeLib.Buchungen
Imports KUeLib.Buchungen.Positionen
Imports System.Data.Common


Namespace Services

    Public Class BuchungspositionenDataService
        Inherits ServiceDefinitions.BuchungspositionenDataService

        Public Sub New(ByVal dataService As DbDataService)
            MyBase.New(dataService)
        End Sub

        Protected Function GetLastInsertId() As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("SELECT last_insert_id()")

            cmd.CommandText = sb.ToString()

            Return CInt(_dataService.ExecuteScalar(cmd))
        End Function

        Public Overrides Function GetPositionenForProcess(ByVal parentId As Integer) As List(Of Buchungsposition)
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder
            Dim lobp As New List(Of Buchungsposition)
            Dim dt As DataTable

            sb.AppendLine("SELECT * FROM buchungen_positionen WHERE bp_parent = @p_parent ORDER BY bp_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_parent", DbType.Int32, parentId))

            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1
                With dt.Rows(i)
                    Dim tempBp As New Buchungsposition
                    tempBp.Id = CInt(Utils.DbNull2Nothing(.Item("bp_id")))
                    tempBp.ParentId = CInt(Utils.DbNull2Nothing(.Item("bp_parent")))
                    tempBp.Bezeichnung = CStr(Utils.DbNull2Nothing(.Item("bp_bezeichnung")))
                    tempBp.Datum = CDate(Utils.DbNull2Nothing(.Item("bp_datum")))
                    tempBp.Betrag = CDbl(Utils.DbNull2Nothing(.Item("bp_betrag")))
                    tempBp.KategorieId = CInt(Utils.DbNull2Nothing(.Item("bp_kategorie")))
                    lobp.Add(tempBp)
                End With
            Next

            Return lobp

        End Function

        Public Overrides Function Save(ByVal buchungsposition As Buchungsposition) As Integer
            If Update(buchungsposition) <= 0 Then _
                Insert(buchungsposition) : buchungsposition.Id = GetLastInsertId()
            Return buchungsposition.Id
        End Function

        Private Function Update(ByVal buchungsposition As Buchungsposition) As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("UPDATE buchungen_positionen SET bp_bezeichnung = @p_bezeichnung, ")
            sb.AppendLine("                                bp_datum = @p_datum, ")
            sb.AppendLine("                                bp_betrag = @p_betrag, ")
            sb.AppendLine("                                bp_kategorie = @p_kategorie ")
            sb.AppendLine("WHERE bp_id = @p_id ")
            sb.AppendLine("  AND bp_parent = @p_parent ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_bezeichnung", DbType.String, buchungsposition.Bezeichnung))
            cmd.Parameters.Add(_dataService.CreateParameter("p_datum", DbType.Date, buchungsposition.Datum))
            cmd.Parameters.Add(_dataService.CreateParameter("p_betrag", DbType.Double, buchungsposition.Betrag))
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, buchungsposition.Id))
            cmd.Parameters.Add(_dataService.CreateParameter("p_parent", DbType.Int32, buchungsposition.ParentId))
            cmd.Parameters.Add(_dataService.CreateParameter("p_kategorie", DbType.Int32, buchungsposition.KategorieId))

            Return _dataService.ExecuteNonQuery(cmd)
        End Function

        Private Function Insert(ByVal buchungsposition As Buchungsposition) As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("INSERT INTO buchungen_positionen (bp_parent, bp_bezeichnung, bp_datum, bp_betrag, bp_kategorie) ")
            sb.AppendLine("VALUES(@p_parent, @p_bezeichnung, @p_datum, @p_betrag, @p_kategorie) ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_bezeichnung", DbType.String, buchungsposition.Bezeichnung))
            cmd.Parameters.Add(_dataService.CreateParameter("p_datum", DbType.Date, buchungsposition.Datum))
            cmd.Parameters.Add(_dataService.CreateParameter("p_betrag", DbType.Double, buchungsposition.Betrag))
            cmd.Parameters.Add(_dataService.CreateParameter("p_parent", DbType.Int32, buchungsposition.ParentId))
            cmd.Parameters.Add(_dataService.CreateParameter("p_kategorie", DbType.Int32, buchungsposition.KategorieId))

            Return _dataService.ExecuteNonQuery(cmd)
        End Function

        Public Overrides Function GetAufteilungssummeForBuchungs(ByVal parentId As Integer) As Double
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("SELECT SUM(bp_betrag) FROM buchungen_positionen WHERE bp_parent = @p_parent ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_parent", DbType.Int32, parentId))

            Return CDbl(Utils.DbNull2Nothing(_dataService.ExecuteScalar(cmd)))
        End Function

        Public Overloads Overrides Sub DeletePosition(ByVal position As Buchungsposition)
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("DELETE FROM buchungen_positionen WHERE bp_id = @p_id")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, position.Id))

            _dataService.ExecuteNonQuery(cmd)
        End Sub

        Public Overrides Function DeletePositionen(ByVal buchungId As Integer) As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("DELETE FROM buchungen_positionen WHERE bp_parent = @p_parent")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_parent", DbType.Int32, buchungId))

            Return _dataService.ExecuteNonQuery(cmd)
        End Function

    End Class

End Namespace
